package com.bsth.repository.schedule;

import com.bsth.entity.Cars;
import com.bsth.entity.schedule.SchedulePlanInfo;
import com.bsth.repository.BaseRepository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

/**
 * Created by xu on 16/6/6.
 */
@Repository
public interface SchedulePlanInfoRepository extends BaseRepository<SchedulePlanInfo, Long> {
	
	@Query(value = "select s from SchedulePlanInfo s where scheduleDate=?1")
	List<SchedulePlanInfo> findByDate(Date date);

    @Query(value = " select " +
            "info.xl as xlId, " +
            "info.xl_name as xlName, " +
            "info.schedule_date as scheduleDate, " +
            "info.lp_name as lpName, " +
            "info.cl as clId, " +
            "info.cl_zbh as clZbh, " +
            "group_concat(info.fcsj order by info.fcno) ccsj, " +
            "group_concat(info.bc_type order by info.fcno) bctype, " +
            "group_concat(distinct info.j order by info.fcno) jsyId, " +
            "group_concat(distinct info.j_gh order by info.fcno) jsyGh, " +
            "group_concat(distinct info.j_name order by info.fcno) jsyName, " +
            "group_concat(distinct info.s order by info.fcno) spyId, " +
            "group_concat(distinct info.s_gh order by info.fcno) spyGh, " +
            "group_concat(distinct info.s_name order by info.fcno) spyName, " +
            "group_concat(info.fcno order by info.fcno) fcno, " +
            "max(info.update_date) as updateDate, " +
            "max(user.user_name) as updateUserName, " +
            "max(info.tt_info_name) as ttInfoName " +
            "from bsth_c_s_sp_info info left join bsth_c_sys_user user on info.update_by = user.id " +
            "left join bsth_c_s_gbi gbi on info.lp = gbi.id " +
            "where info.xl = ?1 and " +
            "info.schedule_date = ?2 " +
            "group by info.xl_name, info.schedule_date, info.lp, info.lp_name, info.cl, info.cl_zbh " +
            "order by info.xl_name, info.schedule_date, gbi.lp_no ", nativeQuery = true)
    List<Object[]> findGroupInfo(Integer xlid, Date scheduleDate);

    @Modifying
    @Query(value = "update " +
            "SchedulePlanInfo scpinfo " +
            "set scpinfo.cl = :p1, scpinfo.clZbh = :p2 " +
            "where scpinfo.xl = :p3 and " +
            "scpinfo.scheduleDate = :p4 and " +
            "scpinfo.lpName = :p5 ")
    int updateGroupInfo_type_1(
            @Param("p1") Integer clid,
            @Param("p2") String clZbh,
            @Param("p3") Integer xlid,
            @Param("p4") Date scheduleDate,
            @Param("p5") String lpName);

    @Modifying
    @Query(value = "update " +
            "SchedulePlanInfo scpinfo " +
            "set scpinfo.fcsj = :p1 " +
            "where scpinfo.xl = :p2 and " +
            "scpinfo.scheduleDate = :p3 and " +
            "scpinfo.lpName = :p4 and " +
            "scpinfo.fcsj = :p5 and " +
            "scpinfo.bcType = :p6 and " +
            "scpinfo.fcno = :p7 " )
    int updateGroupInfo_type_2_4(
            @Param("p1") String fcsj,
            @Param("p2") Integer xlid,
            @Param("p3") Date scheduleDate,
            @Param("p4") String lpName,
            @Param("p5") String fcsj_src,
            @Param("p6") String bcType,
            @Param("p7") Integer fcno);

    @Modifying
    @Query(value = "update " +
            "SchedulePlanInfo scpinfo " +
            "set scpinfo.j = :p1, " +
            "scpinfo.jGh = :p2, " +
            "scpinfo.jName = :p3  " +
            "where scpinfo.xl = :p4 and " +
            "scpinfo.scheduleDate = :p5 and " +
            "scpinfo.lpName = :p6 and " +
            "scpinfo.j = :p7 ")
    int updateGroupInfo_type_3_5_jsy(
            @Param("p1") Integer jsyId,
            @Param("p2") String jsyGh,
            @Param("p3") String jsyName,
            @Param("p4") Integer xlId,
            @Param("p5") Date scheduleDate,
            @Param("p6") String lpName,
            @Param("p7") Integer jsyId_src);

    @Modifying
    @Query(value = "update " +
            "SchedulePlanInfo scpinfo " +
            "set scpinfo.s = :p1, " +
            "scpinfo.sGh = :p2, " +
            "scpinfo.sName = :p3  " +
            "where scpinfo.xl = :p4 and " +
            "scpinfo.scheduleDate = :p5 and " +
            "scpinfo.lpName = :p6 and " +
            "scpinfo.s = :p7 ")
    int updateGroupInfo_type_3_5_spy(
            @Param("p1") Integer spyId,
            @Param("p2") String spyGh,
            @Param("p3") String spyName,
            @Param("p4") Integer xlId,
            @Param("p5") Date scheduleDate,
            @Param("p6") String lpName,
            @Param("p7") Integer spyId_src);
    
    @Query(value="select s from SchedulePlanInfo s where DATE_FORMAT(s.scheduleDate,'%Y-%m-%d') = ?1 order by s.xlBm,clZbh,lp,xlDir")
	List<SchedulePlanInfo> findLineScheduleBc(String scheduleDate);

}
